# -*- encoding: utf-8 -*-

import json
import numpy as np
import xlwings as xw


"""
  从指定的Excel文件中获取指定sheet、cell的数据，并返回 numpy 矩阵
  Args:
    file_path: Excel文件路径
    sheet_name: sheet名称
    start_cell: 起始cell
    end_cell: 结束cell

  Returns:
    numpy矩阵
"""
def get_sheet_matrix(file_path: str, sheet_name: str, start_cell: str, end_cell: str) -> np.ndarray:
    app = xw.App(visible=True, add_book=False, spec='wpsoffice')
    app.display_alerts = False
    app.screen_updating = False
    workbook = app.books.open(file_path)
    sheet = workbook.sheets[sheet_name]

    # 获取指定区域的值，并构建二维列表
    values = sheet.range(start_cell, end_cell).value
    # 将二维列表转为numpy矩阵
    matrix = np.array(values)

    workbook.close()
    app.quit()

    return matrix

"""
  将numpy矩阵转换为json字符串
  Args:
    matrix: numpy矩阵

  Returns:
    json字符串
"""
def matrix_to_json(matrix: np.ndarray) -> str:
    headers = matrix[0]
    data = matrix[1:]

    json_data = []
    for row in data:
        item = {}
    for i in range(len(headers)):
        item[headers[i]] = row[i]
    json_data.append(item)

    json_str = json.dumps(json_data, indent=2, ensure_ascii=False)
    return json_str

if __name__ == '__main__':
    file_path = "/Users/yanyongfeng/platform_manage_us_city_data.xlsx"
    matrix = get_sheet_matrix(file_path, "Result 1", "A1", "B4")
    print(matrix)
    print(matrix_to_json(matrix))
